#ifndef SQL_ADAPTER_H
#define SQL_ADAPTER_H

#include<QSqlDriver>
#include<QSqlDatabase>
#include<QSqlQuery>
#include<QSqlError>
#include<QSqlRecord>
#include<QString>
#include<QLineEdit>
#include<QStringList>
#include<QTableWidget>
#include<QSqlQuery>
#include<QDebug>
#include<QMessageBox>
#include<exception>
#include<bits/stdc++.h>
#include<QSqlTableModel>
#include<QTableView>
#include<mainwindow.h>

QSqlDatabase db;
QSqlQuery* db_helper = nullptr;

QStringList system_tables = {"snapshot", "gs_errors", "gs_source", "all_db_links", "utl_file_dir"};

bool is_system_tables(QString table_name)
{
    return system_tables.contains(table_name);
}

void disconnect_to_sql()
{
    QSqlDatabase::removeDatabase("qt_sql_default_connection");
    QSqlDatabase::removeDatabase("QODBC");
}

bool connect_to_sql(Ui::MainWindow* ui)
{
    //get input information from line edit
    QLineEdit* edit = ui->odbc_input;
    QString DatabaseName = edit->text();
    edit = ui->ip_input;
    QString HostName = edit->text();
    edit = ui->port_input;
    int Port = edit->text().toInt();
    edit = ui->uid_input;
    QString UserName = edit->text();
    edit = ui->pwd_input;
    QString Password = edit->text();

    //statr connect
    disconnect_to_sql();

    db = QSqlDatabase::addDatabase("QODBC");
    db.setHostName(HostName);
    db.setPort(Port);
    db.setDatabaseName(DatabaseName);
    db.setUserName(UserName);
    db.setPassword(Password);
    bool connect_status = db.open();
    if(connect_status)
    {
        ui->status->setText("connected");
        return true;
    }
    else
    {
        QSqlError err = db.lastError();
        ui -> status -> setText(err.text());
        return false;
    }
}

QStringList get_all_tables()
{
    return db.tables();
}

void start_sql_query(QString& sql)
{
    if(db_helper == nullptr)
    {
        db_helper = new QSqlQuery(db);
    }
    if(db_helper -> exec(sql) == false)
    {
        QMessageBox mbox;
        mbox.setText(db_helper -> lastError().text());
        mbox.exec();
    }
}

void add_new_table(QTableWidget* new_table, QString table_name)
{
    int primary_key_num = 0;
    QStringList primary_keys;

    for(int i = 0; i < new_table -> rowCount(); i++)
    {
        if(new_table -> item(i, 2) -> text().contains("PRIMARY KEY"))
        {
            primary_key_num++;
            primary_keys.append(new_table -> item(i, 0) -> text());
        }
    }

    //get sql word
    QString sql_str = "CREATE TABLE " + table_name + "(";
    if(primary_key_num == 1)
    {
        for(int i = 0; i < new_table -> rowCount(); i++)
        {
            sql_str = sql_str + new_table -> item(i, 0) -> text() + " " + new_table -> item(i, 1) -> text();
            if(new_table -> item(i, 2) -> text() != "无")
            {
                sql_str = sql_str + " " + new_table -> item(i, 2) -> text();
            }
            if(i != new_table -> rowCount() - 1)
            {
                sql_str = sql_str + ",";
            }
        }
        sql_str = sql_str + ");";
    }
    else
    {
        for(int i = 0; i < new_table -> rowCount(); i++)
        {
            sql_str = sql_str + new_table -> item(i, 0) -> text() + " " + new_table -> item(i, 1) -> text();
            if(new_table -> item(i, 2) -> text() != "无" && new_table -> item(i, 2) -> text() != "PRIMARY KEY")
            {
                sql_str = sql_str + " " + new_table -> item(i, 2) -> text();
            }
            sql_str = sql_str + ",";
        }

        sql_str = sql_str + "PRIMARY KEY(";
        for(int i = 0; i < primary_keys.length(); i++)
        {
            sql_str = sql_str + primary_keys[i];
            if(i != primary_keys.length() - 1)
            {
                sql_str = sql_str + ",";
            }
        }

        sql_str = sql_str + "));";
    }

    //start sql request
    start_sql_query(sql_str);
    db_helper -> clear();
}

void delete_table(QString table_name)
{
    QString sql_str = "DROP TABLE " + table_name + ";";
    start_sql_query(sql_str);
}

void clear_table(QString table_name)
{
    QString sql_str = "DELETE * FROM " + table_name + ";";
    start_sql_query(sql_str);
}

class SQLHelper
{
public:
    static void FillTableModel(QTableWidget* Table, QString TableName, MainWindow* ui = nullptr)
    {
        Table -> clear();
        Table -> setRowCount(0);

        QString Command = "select * from " + TableName;
        start_sql_query(Command);

        if(db_helper -> isActive())
        {
            QSqlRecord rec = db_helper -> record();
            int columns = rec.count();

            QStringList ColumnNames;

            for(int i = 0; i < db_helper -> record().count(); i++)
            {
                ColumnNames.append(db_helper -> record().fieldName(i));
            }

            Table -> setColumnCount(db_helper -> record().count());
            Table -> setHorizontalHeaderLabels(ColumnNames);

            while (db_helper -> next())
            {
                Table -> insertRow(Table -> rowCount());

                for(int i = 0; i < columns; i++)
                {
                    Table -> setItem(Table -> rowCount() - 1, i, new QTableWidgetItem(db_helper -> value(i).toString()));
                }
            }
        }
        else
        {
        }

        db_helper -> clear();
    }
};

#endif // SQL_ADAPTER_H
